﻿//Dùng tại:
//      + frmNhapVeHuy_DL
//      + frmNhapVeHuy_CB
//      + frmTongHopTT

using System;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Collections.Generic;

namespace prjKQXS_DLL.Truyen_thong
{
    public class clsHuyVe
    {
        SqlConnection conn = new SqlConnection(BienTc.connString);

        //Cài đặt event OnError để bắt các lỗi chưa được xử lý
        public delegate void OnErrorHandler(Exception e);
        public event OnErrorHandler OnError;

        //Cài đặt event OnAction để thông báo các trạng thái kết quả
        public delegate void OnActionHandler(SqlException e);
        public event OnActionHandler OnAction;


        /// <summary>
        /// Dùng tại frmTongHopTT.In_TongHop(), frmNhapVeHuy_DL.sbtnInChiTiet_Click()
        /// </summary>
        /// <param name="MaCB"></param>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <returns></returns>
        public DataTable rptTongHopCTTT_DL(string MaCB, DateTime TuNgay, DateTime DenNgay, ref string TyLe)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_DL_rptTongHopCTTT", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@MaCB", MaCB);
                da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                da.Fill(dt);

                DataView dv = dt.DefaultView;
                dv.Sort = "MaDL ASC";
                dt = dv.ToTable();

                TyLe = TinhTyLe1(ref dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }


        /// <summary>
        /// Dùng tại frmNhapVeHuy_DV, frmTongHopTT
        /// </summary>
        /// <param name="MaDV">Mã đơn vị. Giá trị là '%' hoặc mã đơn vị tương ứng</param>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <param name="TyLe"></param>
        /// <param name="VietBangSo"></param>
        /// <returns></returns>
        public DataTable rptTongHop_CB(char MaDV, DateTime TuNgay, DateTime DenNgay, ref string TyLe, ref string VietBangSo)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_CB_TongHop", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@MaDV", MaDV);
                da.Fill(dt);
                
                if(dt.Columns.Contains("TyLe"))
                    dt.Columns.Remove("TyLe");
                dt.Columns.Add("TyLe", Type.GetType("System.String"));

                TinhTyLe2(ref dt, ref TyLe, ref VietBangSo);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }


        /// <summary>
        /// Dùng tại frmNhapVeHuy_DL, frmNhapVeHuy_CB, frmBaoCaoThanhToan
        /// </summary>
        /// <param name="MaDV">Mã đơn vị. Giá trị là '%' hoặc mã đơn vị tương ứng</param>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <param name="TyLe"></param>
        /// <param name="DLorCB">Nếu là frmNhapVeHuy_DL hoặc frmTongHopTT thì là _DL, nếu là frmNhapVeHuy_CB thì là _CB</param>
        /// <returns></returns>
        public DataTable rptTongHopTT(char MaDV, DateTime TuNgay, DateTime DenNgay, ref string TyLe, string DLorCB)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                if (conn.State != ConnectionState.Open) conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_rptTongHopTT" + DLorCB, conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;

                //if (DLorCB == "_DL")
                //{
                //    TuNgay = DenNgay;
                //}
                //else
                //{
                    da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                    da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                //}
                da.SelectCommand.Parameters.AddWithValue("@MaDV", MaDV);
                da.Fill(dt);

                TyLe = TinhTyLe1(ref dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }

        private string TinhTyLe1(ref DataTable dt)
        {
            decimal VeNhan = 0, TongVeNhan = 0;
            decimal VeBan = 0, TongVeBan = 0;

            // sửa phần thập phân
            if (dt.Columns.Contains("TyLe")) dt.Columns.Remove("TyLe");
            dt.Columns.Add("TyLe", typeof(string));
            foreach (DataRow row in dt.Rows)
            {
                if (!string.IsNullOrEmpty(row["SLVeNhan"].ToString().Trim())
                    && (decimal)clsCommon.Parse(row["SLVeNhan"].ToString(), 3) != 0
                    && !string.IsNullOrEmpty(row["SLVeBan"].ToString().Trim()))
                {
                    if (string.IsNullOrEmpty(row["SLVeNhan"].ToString().Trim())) row["SLVeNhan"] = "0";
                    VeNhan = (decimal)clsCommon.Parse(row["SLVeNhan"].ToString(), 3);
                    VeBan = (decimal)clsCommon.Parse(row["SLVeBan"].ToString(), 3);
                    TongVeNhan += VeNhan;
                    TongVeBan += VeBan;

                    row["TyLe"] = (Math.Round(VeBan / VeNhan * 100, 1)).ToString();
                }
            }

            string TyLe = "";
            if (TongVeNhan != 0) TyLe = (Math.Round(TongVeBan / TongVeNhan * 100, 1)).ToString();

            return TyLe;
        }

        private void TinhTyLe2(ref DataTable dt, ref string TyLe, ref string VietBangSo)
        {
            int i, j;
            for (int k = 0; k < dt.Rows.Count - 1; k++)
            {
                i = k; 
                j = k + 1;

                if (Convert.ToInt32(dt.Rows[i]["MaDV"]) == Convert.ToInt32(dt.Rows[j]["MaDV"]))
                {
                    char[] trimchar = new char[]{' ', '0'};
                    if (string.IsNullOrEmpty(dt.Rows[i]["SLVeNhan"].ToString().Trim(trimchar))) 
                        dt.Rows.RemoveAt(i);
                    else dt.Rows.RemoveAt(j);
                    k--;
                }
            }

            decimal venhan = 0;
            decimal docso1 = 0;
            decimal veban = 0;

            foreach (DataRow row in dt.Rows)
            {
                if (row["SLVeNhan"] != null
                    && (decimal)clsCommon.Parse(row["SLVeNhan"].ToString(), 3) != 0
                    && row["SLVeNhan"].ToString() != ""
                    && row["SLVeBan"] != null)
                {
                    if (row["SLVeNhan"] == null || row["SLVeNhan"].ToString() == "")
                        row["SLVeNhan"] = "0";
                    venhan += (decimal)clsCommon.Parse(row["SLVeNhan"].ToString(), 3);
                    veban += (decimal)clsCommon.Parse(row["SLVeBan"].ToString(), 3);
                    decimal SLVeNhan = Convert.ToDecimal(row["SLVeNhan"]);
                    decimal SLVeBan = Convert.ToDecimal(row["SLVeBan"]);
                    row["TyLe"] = (SLVeBan / SLVeNhan * 100).ToString("F1", CultureInfo.CreateSpecificCulture("de-DE"));
                    docso1 = veban * (int)clsCommon.Parse(row["GiaVe"].ToString(), 0); ;
                }
            }

            if (venhan != 0)
            {
                TyLe = (Math.Round(veban / venhan * 100, 1)).ToString("F1", CultureInfo.CreateSpecificCulture("de-DE"));
            }

            VietBangSo = clsCommon.Docso(docso1);
        }


        /// <summary>
        /// Dùng tại rptThongKeTT_DV_Ngay
        /// </summary>
        /// <param name="MaDV"></param>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <returns></returns>
        public DataTable rptThongKeTT_DV_Ngay(char MaDV, DateTime TuNgay, DateTime DenNgay, ref string TyLe)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_rptThongKeTT_DV_Ngay", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@MaDV", MaDV);
                da.Fill(dt);

                TyLe = TinhTyLe1(ref dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }


        /// <summary>
        /// Dùng tại rptThongKeTT_CB_Ngay
        /// </summary>
        /// <param name="MaCB"></param>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <returns></returns>
        public DataTable rptThongKeTT_CB_Ngay(string MaCB, DateTime TuNgay, DateTime DenNgay, ref string TyLe)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_rptThongKeTT_CB_Ngay", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@MaCB", MaCB);
                da.Fill(dt);
                TyLe = TinhTyLe1(ref dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }


        /// <summary>
        /// Dùng tại rptThongKeTT_CT
        /// </summary>
        /// <param name="TuNgay"></param>
        /// <param name="DenNgay"></param>
        /// <param name="TyLe"></param>
        /// <returns></returns>
        public DataTable rptThongKeTT_CT(DateTime TuNgay, DateTime DenNgay, ref string TyLe)
        {
            DataTable dt = new DataTable("TruyenThong");
            try
            {
                SqlConnection conn = new SqlConnection(BienTc.connString);
                conn.Open();
                SqlDataAdapter da = new SqlDataAdapter("proc_HuyVe_rptThongKeTT_CT", conn);
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
                da.SelectCommand.Parameters.AddWithValue("@TuNgay", TuNgay.Date);
                da.SelectCommand.Parameters.AddWithValue("@DenNgay", DenNgay.Date);
                da.Fill(dt);

                TyLe = TinhTyLe1(ref dt);
            }
            catch (SqlException e)
            {
                OnAction(e);
            }
            catch (Exception e)
            {
                OnError(e);
            }

            return dt;
        }
    }
}
